Information is conveniently stored in repositories such as databases. Stored information is accessed and updated with applications executing in a database management system (DBMS). A DBMS may include one or more database servers that manage access to a database. A DBMS thus functions both to efficiently store information in one or more containers and as a database server to efficiently retrieve the information therefrom, on request. Each of the data containers of a database contains records. The data within each record is organized into one or more attribute fields. Various database architectures exist.
The data containers of relational databases are tables and the records stored therein are rows. The database can contain virtually any number of tables, the attributes of which, are referred to as columns. The data containers of an object oriented database are object classes, the records of which comprise objects, which have attributes referred to as object attributes. Other database architectures also exist. For simplicity and unity herein, a relational database is discussed, solely by way of example.
To retrieve data stored in a database, queries are submitted to a database server, which computes the query and returns the data requested therewith. Query statements submitted to a database server typically conform to the syntax of a particular database language supported by the DBMS. SQL (Structured Query Language) and PL/SQL (Procedural Language/SQL) are well known examples of such languages. Other database languages, some of them proprietary, are also used. For simplicity and unity, and solely by way of example, SQL statements are discussed herein. Unless otherwise indicated, terms and phrases are used herein in contexts explained in the four paragraphs that follow, below.
Views can allow the results of some queries to be computed more efficiently. The term “view” refers to a predefined query that is treated as a table. A view is thus essentially a virtual table and may be referenced by a database statement as if the view were an actual table. Tables that actually store the data associated with a view are referred to as “base tables.” Base tables are referenced, directly or indirectly, by the predefined query. To generate the rows of a view, data is extracted and derived from the base tables. Changing data in a view's base table alters the data returned for a view.
Unless otherwise indicated, the term “Materialized View” (MV) refers to a view for which the results of the predefined query are stored as precomputed values. Materializing a materialized view obviates multiple executions of queries that may require significant time to compute. Thus, materializing a materialized view may allow faster and more efficient access to data.
Further, queries may be dynamically and transparently rewritten to reference one or more materialized views rather than base tables, which can boost database performance by allowing faster and more efficient computing of queries. Unless otherwise indicated, the term “query rewrite” herein refers to transforming a SQL query, expressed in terms of tables and views, into a statement that accesses the materialized views associated with those tables and views, instead. One or more other materialized views may be nested within a materialized view and may be used to facilitate some query rewrites. These nested materialized views may be nested as a sort of “child” within a “parent” materialized view.
Multiple nested materialized views may exist within a parent materialized view, each nested at a distinct nesting level thereof. The parent materialized view refers to the rows of a base table. In a hypothetical example materialized view having nested materialized views at N nesting levels, the first level materialized view refers to the parent materialized view. The second level nested materialized view refers to the first level materialized view. The third level nested materialized view refers to the second level materialized view and so forth, through an Nth level materialized view, which refers to the materialized view in the nesting level immediately preceding the Nth nesting level. As used herein, the term cardinality, as applied to a materialized view, including nested materialized views, refers to the number of rows of a base table or other materialized view, which is referenced with that materialized view.
As used herein, a subordinate set of a particular database object is essentially the set of all the database objects that refer, directly or indirectly, to that particular database object. For a base table, a particular materialized view that directly refers (i.e. whose definition refers) to the base table is in the subordinate set of the base table. A materialized view that refers to the particular materialized indirectly refers to the base table and is in the subordinate set of the base table. Similarly, the materialized views whose definition directly or indirectly refers to a particular materialized view is in the subordinate set of the materialized view. Nested materialized views are in the subordinate sets of their parent materialized view, as well as any nested materialized view between it and its parent.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section. Similarly, issues identified with respect to one or more approaches should not assume to have been recognized in any prior art on the basis of this section, unless otherwise indicated.
Based on the foregoing, it would be useful to facilitate query rewrite using nested materialized views.